package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.Set;

import objects.ActorObject;
import objects.CategoryObject;
import objects.EpisodeObject;
import objects.SeasonObject;
import objects.SerialContainer;
import objects.SerialObject;
import utils.DaoConstants;

public class DaoSearch extends DaoGeneric<SerialObject> {

	private final static String TABLENAME = DbTables.USER_LOGIN.toString();

	public DaoSearch(java.sql.Connection con) {
		super(con, TABLENAME);
	}

	public SerialContainer getSerialList(String titelSearchStr) {
		String query = "select * from " + DaoConstants.SERIAL + " FULL JOIN "
				+ DaoConstants.SEASON + " on " + DaoConstants.SERIAL + "."
				+ DaoConstants.ID_NUM_SL + " = " + DaoConstants.SEASON + "."
				+ DaoConstants.SEASON2SERIAL + " FULL JOIN "
				+ DaoConstants.EPISODE + " on " + DaoConstants.SEASON + "."
				+ DaoConstants.ID_NUM_SN + " = " + DaoConstants.EPISODE + "."
				+ DaoConstants.EPISODE_SEASON + " FULL JOIN "
				+ DaoConstants.ACTOR + " on " + DaoConstants.ACTOR + "."
				+ DaoConstants.ID_NUM_AC + " = " + DaoConstants.SERIAL + "."
				+ DaoConstants.MTM_ACTOR + " FULL JOIN "
				+ DaoConstants.CATEGORY + " on " + DaoConstants.SERIAL + "."
				+ DaoConstants.SERIAL2CATEGORY + " = " + DaoConstants.CATEGORY
				+ "." + DaoConstants.ID_NUM_CY + " where "
				+ DaoConstants.SERIAL + "." + DaoConstants.SERIAL_TITLE + " like ? ";
		// SerialContainer Object
		SerialContainer serialConainer = new SerialContainer();

		// Serial
		SerialObject sObject = null;
		Set<SerialObject> searchList = new LinkedHashSet<SerialObject>();

		// Season
		SeasonObject seasonObject = null;
		Set<SeasonObject> seasonList = new LinkedHashSet<SeasonObject>();

		// Episode
		EpisodeObject epObject = null;
		Set<EpisodeObject> episodeList = new LinkedHashSet<EpisodeObject>();

		// Actor
		ActorObject actorObject = null;
		Set<ActorObject> actorList = new LinkedHashSet<ActorObject>();

		// Category
		CategoryObject catObject = null;
		Set<CategoryObject> catList = new LinkedHashSet<CategoryObject>();

		try {

			PreparedStatement search = con.prepareStatement(query);

			search.setString(1, "%" + titelSearchStr + "%");

			ResultSet res = search.executeQuery();

			while (res.next()) {
				sObject = new SerialObject(res.getInt(DaoConstants.ID_NUM_SL),
						res.getString(DaoConstants.SERIAL_TITLE),
						res.getString(DaoConstants.ORG_TITLE),
						res.getInt(DaoConstants.YEAR_START),
						res.getInt(DaoConstants.YEAR_END),
						res.getString(DaoConstants.DESCR),
						res.getInt(DaoConstants.SERIAL2CATEGORY),
						res.getString(DaoConstants.PIC_PATH));
				searchList.add(sObject);
				if (sObject.getSerialTitle() != null) {
					actorList.add(actorObject);
				}

				seasonObject = new SeasonObject(
						res.getInt(DaoConstants.ID_NUM_SN),
						res.getInt(DaoConstants.SEASON_NUM),
						res.getString(DaoConstants.SEASON_NAME),
						res.getInt(DaoConstants.SEASON_YEAR),
						res.getInt(DaoConstants.SEASON2SERIAL));
				seasonList.add(seasonObject);
				if (seasonObject.getSeasonName() != null) {
					actorList.add(actorObject);
				}

				epObject = new EpisodeObject(
						res.getInt(DaoConstants.ID_NUM_SP),
						res.getInt(DaoConstants.EPISODE_NUM),
						res.getString(DaoConstants.EPISODE_NAME),
						res.getString(DaoConstants.EPISODE_DESC),
						res.getInt(DaoConstants.EPISODE_SEASON),
						res.getDate(DaoConstants.EPISODE_PRIME));
				episodeList.add(epObject);
				if (epObject.getEpisodeName() != null) {
					actorList.add(actorObject);
				}

				actorObject = new ActorObject(
						res.getInt(DaoConstants.ID_NUM_AC),
						res.getString(DaoConstants.ACTOR_LASTNAME),
						res.getString(DaoConstants.ACTOR_FIRSTNAME),
						res.getString(DaoConstants.ACTOR_DESC));
				if (actorObject.getActorFirstName() != null
						&& actorObject.getActorLastName() != null) {
					actorList.add(actorObject);
				}

				catObject = new CategoryObject(
						res.getInt(DaoConstants.ID_NUM_CY),
						res.getString(DaoConstants.CATEGORY_NAME),
						res.getString(DaoConstants.CATEGORY_DESC));
				catList.add(catObject);
				if (catObject.getCategoryName() != null) {
					actorList.add(actorObject);
				}
			}

			actorList.removeAll(Collections.singleton(null));
			episodeList.removeAll(Collections.singleton(null));

			serialConainer.setSerialList(searchList);
			serialConainer.setSeasonList(seasonList);
			serialConainer.setEpisodeList(episodeList);
			serialConainer.setActorList(actorList);
			serialConainer.setCatList(catList);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return serialConainer;
	}

}
